﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace ORM
{
	/// <summary>
	/// 针对SqlServer的逻辑
	/// </summary>
	public class SqlOperation : DbOperation
	{
		public SqlOperation(string connStr) : base(new SqlConnection(connStr)) { }
		public SqlOperation(string host, string account, string password, string db) : base(new SqlConnection("server={0};database={1};uid={2};pwd={3};pooling=true;min pool size=1;max pool size=10;connection reset=true".Fmt(host, db, account, password))) { }

		/// <summary>
		/// 针对数据库的实体名称包裹
		/// </summary>
		/// <param name="field_or_table_code">表名或字段名</param>
		/// <returns></returns>
		public override string DBCode(string field_or_table_code)
		=> @"""{0}""".Fmt(field_or_table_code);

		#region SqlServer的分页逻辑
		/// <summary>
		/// 分页(不使用参数查询机制)
		/// </summary>
		/// <param name="obj">分页的类型实例</param>
		/// <param name="indexBasedOne">页码(首页为1)</param>
		/// <param name="perPage">每页记录数</param>
		/// <param name="sqlWhere">查询条件where子句</param>
		/// <param name="orders">排序设置(默认id降序)</param>
		/// <returns>对象集合</returns>
		internal override IEnumerable<DataObject> PageBySqlWhere(DataObject obj, int indexBasedOne = 1, int perPage = 10, string sqlWhere = null, params (string field,string order)[] orders)
		{
			string cID = DBCode(DataObject.FIELD_ID), cTABLE = DBCode(obj.TABLE_NAME);

			if (orders == null || orders.Count() == 0) orders = new[] { (DataObject.FIELD_ID, "desc") };
			var order = "order by " + orders.Select(fd => $"{DBCode(fd.field)} {fd.order}").JoinBy(",");

			if (indexBasedOne < 2) return obj.GetBySqlWhere(sqlWhere, perPage, orders);

			var skip = (indexBasedOne - 1) * perPage;
			if (string.IsNullOrEmpty(sqlWhere))     //仅id not in
				sqlWhere = $"{cID} not in (select top {skip} {cID} from {cTABLE} {order})";
			else                    //(sqlWhere) and id not in。这里的括号是为了处理sqlWhere里面的or连接，不要让sqlWhere里的条件覆盖了id not in的逻辑
				sqlWhere = $"({sqlWhere}) and {cID} not in (select top {skip} {cID} from {cTABLE} where {sqlWhere} {order})";

			return obj.GetBySqlWhere(sqlWhere, perPage, orders);
		}

		/// <summary>
		/// 分页(使用参数查询机制)
		/// </summary>
		/// <param name="obj">分页的类型实例</param>
		/// <param name="indexBasedOne">页码(首页为1)</param>
		/// <param name="perPage">每页记录数</param>
		/// <param name="conditions">查询条件。为了简化设计与兼顾灵活性，所有条件关系为单层单连接，如果要混合组合和优先级，则选择PageBySqlWhere</param>
		/// <param name="and_or"></param>
		/// <param name="orders">排序设置(默认id降序)</param>
		/// <returns>对象集合</returns>
		internal override IEnumerable<DataObject> PageByParameter(DataObject obj, int indexBasedOne = 1, int perPage = 10, IEnumerable<(string field,string op, object value)> conditions = null, string and_or = "and", params (string field,string order)[] orders)
		{
			string cID = DBCode(DataObject.FIELD_ID), cTABLE = DBCode(obj.TABLE_NAME);

			if (orders == null || orders.Count() == 0) orders = new[] { (DataObject.FIELD_ID, "desc") };
			var order = "order by " + orders.Select(fd => $"{DBCode(fd.field)} {fd.order}").JoinBy(",");

			if (indexBasedOne < 2) return obj.GetByParameter(conditions, and_or, perPage, orders);

			string sqlWhere = null;
			Dictionary<string, object> parameters = null;
			if (conditions?.Count() > 0)       //将conditions里的每个字段关系转成相应的sql字符串，以及对应的参数
				sqlWhere = obj.sqlWhereAndParams(conditions, and_or, out parameters);

			var skip = (indexBasedOne - 1) * perPage;
			if (string.IsNullOrEmpty(sqlWhere))     //仅id not in
				sqlWhere = $"where {cID} not in (select top {skip} {cID} from {cTABLE} {order})";
			else                //(sqlWhere) and id not in。这里的括号是为了处理sqlWhere里面的or连接，不要让sqlWhere里的条件覆盖了id not in的逻辑
				sqlWhere = $"where ({sqlWhere}) and {cID} not in (select top {skip} {cID} from {cTABLE} where {sqlWhere} {order})";

			string sql = $"select top {perPage} * from {cTABLE} {sqlWhere} {order}";
			var table = TableByKV(sql, parameters);
			var result = obj.fillConcret(table);
			return result;
		}
		#endregion

		#region 原生适配(兜底功能)
		/// <summary>
		/// 初始化Command
		/// </summary>
		/// <param name="sql"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		protected override IDbCommand initCommand(string sql, params IDataParameter[] parameters)
		{
			IDbCommand cmd = new SqlCommand(sql, Connection as SqlConnection);

			if (parameters != null)
				foreach (IDataParameter param in parameters)
					cmd.Parameters.Add(param);

			if (Transaction != null)
				cmd.Transaction = Transaction;

			return cmd;
		}

		/// <summary>
		/// 把Dictionary转换成查询参数
		/// </summary>
		/// <param name="parameters"></param>
		/// <returns></returns>
		protected override IEnumerable<IDbDataParameter> ConvertParameters(Dictionary<string, object> parameters)
		{
			List<IDbDataParameter> ps = new List<IDbDataParameter>();

			if (parameters == null) return null;

			foreach (var param in parameters) ps.Add(new SqlParameter(param.Key, param.Value));
			return ps;
		}

		/// <summary>
		/// 从select-sql获取数据集
		/// </summary>
		/// <param name="sql">select-sql</param>
		/// <param name="parameters">查询参数</param>
		/// <param name="transaction">事务</param>
		/// <returns>结果数据集</returns>
		public override DataSet DataSetByDataParameter(string sql, params IDataParameter[] parameters)
		{
			IDbCommand cmd = initCommand(sql, parameters);
			IDataAdapter adapter = new SqlDataAdapter(cmd as SqlCommand);

			DataSet ds = new DataSet();
			adapter.Fill(ds);
			return ds;
		}
		#endregion

		#region 表间关系相关
		/// <summary>
		/// 获取指定表的所有外键
		/// </summary>
		/// <param name="table"></param>
		/// <returns>外键字段名集合</returns>
		internal override Dictionary<string, string> ForeignKeys(string table)
		{
			//最好写成视图来调用
			string sql = @"select
C.name as fk, object_name(B.referenced_object_id) as target
from sys.foreign_keys A
inner join sys.foreign_key_columns B on A.object_id=B.constraint_object_id
inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id 
inner join sys.columns D on B.referenced_object_id=D.object_id and B.referenced_column_id=D.column_id
where object_name(B.parent_object_id)=@tbl";

			var reader = ExecuteReaderByDataParameter(sql, null, new SqlParameter("tbl", table));
			var result = new Dictionary<string, string>();

			while (reader.Read()) result[reader.GetString(0)] = reader.GetString(1);
			reader.Close();
			return result;
		}

		/// <summary>
		/// 获取指定表的子表及相关外键
		/// </summary>
		/// <param name="table">主表</param>
		/// <returns>子表及其相关外键</returns>
		internal override Dictionary<string, List<string>> ChildrenTableAndFKs(string table)
		{
			//最好写成视图来调用
			string sql = @"select
object_name(B.parent_object_id) as child_table,
C.name as fk
from sys.foreign_keys A
inner join sys.foreign_key_columns B on A.object_id=B.constraint_object_id
inner join sys.columns C on B.parent_object_id=C.object_id and B.parent_column_id=C.column_id 
inner join sys.columns D on B.referenced_object_id=D.object_id and B.referenced_column_id=D.column_id
where object_name(B.referenced_object_id)=@main_table";

			var reader = ExecuteReaderByDataParameter(sql, null, new SqlParameter("main_table", table));
			var result = new Dictionary<string, List<string>>();

			try
			{
				while (reader.Read())
				{
					var child_table = reader.GetString(0);
					if (result.ContainsKey(child_table))
						result[child_table].Add(reader.GetString(1));
					else
						result[child_table] = new List<string> { reader.GetString(1) };
				}
			}
			catch { }

			reader.Close();
			return result;
		}
		#endregion
	}
}